/*
	exec sp_get_All_Approved_Line_Items 136, 515, null
*/
 
 ALTER               proc sp_get_All_Approved_Line_Items
 ( @salesOrder_Id int, @vendor_address_Id int, @SCHEDULED_BEFORE VARCHAR(30) )
 AS
 	declare @results Table( lineItemId  int, qtyToShip int, SalesOrder_ID int, COOL_Line_Item_No int, Product_ID int, COOL_Qty int, COOL_Unit_Cost float,
 	COOL_Amount float, COOL_Requested_Date datetime, COOL_Booking_Date datetime, COOL_Sugg_Schedule_Date_With_Substitutables datetime,
 	COOL_Sugg_Schedule_Date_Without_Substitutables datetime, COOL_Schedule_Date datetime, COOL_Revised_Date datetime, COOL_Is_Cancel bit,
 	COOL_Status varchar(50), COOL_Taxable bit, COOL_Description varchar(200), COOL_PO_Line_Item_No int, 
 	Part_Number varchar(50), Mkt_Part_Number varchar(50), Customer_Part_Number varchar(50), Qty_Available int, Customer_Label_Number varchar(50), Parent_Part_Number varchar(50))
 		
 	declare @temp_lineItem		int
 	declare @shipped_qty_sum 	int
 	declare @qty_to_be_shipped 	int
 
 	declare @COOL_Line_Item_No 	int
 	declare @Product_ID		int	
 	declare @COOL_Qty		int
 	declare @COOL_Unit_Cost		float
 	declare @COOL_Amount		float
 	declare @COOL_Requested_Date	datetime
 	declare @COOL_Booking_Date	datetime
 	declare @COOL_Sugg_Schedule_Date_With_Substitutables    datetime
 	declare @COOL_Sugg_Schedule_Date_Without_Substitutables datetime
 	declare @COOL_Schedule_Date 	datetime
 	declare @COOL_Revised_Date 	datetime
 	declare @COOL_Is_Cancel		bit
 	declare @COOL_Status 		varchar(50)
 	declare @COOL_Taxable 		bit
 	declare @COOL_Description 	varchar(200)
 	declare @COOL_PO_Line_Item_No 	int
 
 	declare @mfg_part_number 	varchar(50)
 	declare @mkt_part_number 	varchar(50)
 	declare @cutomer_part_number 	varchar(50)
 	declare @temp_product_type	varchar(50)
 	declare @customer_id		int
 	declare @temp_process_step_id   int
 	declare @parent_product_id	int
 	declare @qty_available		int
	declare @cutomer_label_number 	varchar(50)
	declare @odm_id 		int 
	declare	@parent_part_number	varchar(50)
	declare @is_sample_sales_order bit

	set @is_sample_sales_order = 1
	select @is_sample_sales_order = SO_Is_Sample from Sales_Order WHERE SalesOrder_ID = @salesOrder_Id

 
	IF ( @SCHEDULED_BEFORE IS NOT NULL AND LEN(LTRIM(RTRIM(@SCHEDULED_BEFORE))) > 0 )
	 BEGIN
	 	declare lineItemCursor cursor for 
	 	SELECT LineItem_ID FROM Customer_Open_Order_LineItem WHERE SalesOrder_ID = @salesOrder_Id  and ( COOL_Status = 'APPROVED' or COOL_Status = 'HOLD' )
			AND ISNULL(COOL_REVISED_DATE, COOL_SCHEDULE_DATE) < CAST(@SCHEDULED_BEFORE AS DATETIME)
 	 END
	ELSE
	 BEGIN
		declare lineItemCursor cursor for 
	 	SELECT LineItem_ID FROM Customer_Open_Order_LineItem WHERE SalesOrder_ID = @salesOrder_Id  and ( COOL_Status = 'APPROVED' or COOL_Status = 'HOLD' )			
	 END
 	open lineItemCursor
 	fetch next from lineItemCursor into @temp_lineItem
 
 	while @@fetch_status = 0
 	begin
 		
 		set @shipped_qty_sum = ( select  isnull(sum(CSLIT_Qty_Shipped), 0) from Customer_Shipping_Line_Item_Table where LineItem_ID = @temp_lineItem AND CSLIT_Status != 'CANCELLED' )
 		set @qty_to_be_shipped  = ( select  (avg(COOL_Qty) - @shipped_qty_sum) from Customer_Open_Order_LineItem where LineItem_ID = @temp_lineItem )
 
 		if( @qty_to_be_shipped > 0 )
 		begin				
 			-- insert into @results ( lineItemId, qtyToShip ) values ( @temp_lineItem, @qty_to_be_shipped )
 			-- convert (varchar, cst.Timestamp, 101)
 			SELECT  @COOL_Line_Item_No = COOL_Line_Item_No, @Product_ID = Product_ID, 
 			@COOL_Qty = COOL_Qty, @COOL_Unit_Cost = COOL_Unit_Cost, @COOL_Amount = COOL_Amount, 
 			@COOL_Requested_Date = convert (varchar, COOL_Requested_Date, 101), @COOL_Booking_Date = convert (varchar, COOL_Booking_Date, 101), 
 			@COOL_Sugg_Schedule_Date_With_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_With_Substitutables, 101) , 
 			@COOL_Sugg_Schedule_Date_Without_Substitutables = convert (varchar, COOL_Sugg_Schedule_Date_Without_Substitutables, 101), 
 			@COOL_Schedule_Date = convert (varchar, COOL_Schedule_Date, 101), @COOL_Revised_Date = convert (varchar, COOL_Revised_Date, 101), 
 			@COOL_Is_Cancel = COOL_Is_Cancel, @COOL_Status = COOL_Status, 
 			@COOL_Taxable = COOL_Taxable, @COOL_Description = COOL_Description, 
 			@COOL_PO_Line_Item_No = COOL_PO_Line_Item_No, @cutomer_part_number = COOL_CUSTOMER_PARTNUMBER FROM Customer_Open_Order_LineItem WHERE LineItem_ID = @temp_lineItem
 
 			if ( ( @COOL_Schedule_Date is null ) And ( @COOL_Sugg_Schedule_Date_Without_Substitutables is not null ) )
 			begin
 				set @COOL_Schedule_Date = @COOL_Sugg_Schedule_Date_Without_Substitutables
 			end
 
 			if ( ( @COOL_Sugg_Schedule_Date_Without_Substitutables is null ) And ( @COOL_Schedule_Date is not null ) ) 
 			begin
 				set @COOL_Sugg_Schedule_Date_Without_Substitutables = @COOL_Schedule_Date
 			end

			IF( @COOL_Revised_Date IS NOT NULL )
			BEGIN
				SET @COOL_Schedule_Date = @COOL_Revised_Date
			END

 			SELECT @mfg_part_number = Part_Number, @mkt_part_number = P_Marketing_Part_Number from product where Product_Id = @Product_ID
 			 			
 			select @temp_product_type = isnull(p_type,'CHIP') from product where Product_Id = @Product_ID
 
 			if( @temp_product_type = 'CHIP' )
 			begin
 				select @temp_process_step_id = ProcessStep_ID,  @parent_part_number = Part_Number  from Product where Product_Id = @Product_ID
 			end
 			else
 			begin
 				select @parent_product_id = P_Parent_Product_ID  from product where Product_Id = @Product_ID
 	
 				if( @parent_product_id = -1)
 				begin	
 					select @temp_process_step_id = ProcessStep_ID,  @parent_part_number = Part_Number  from Product where Product_Id = @Product_ID
 				end
 				else		
 				begin
 					select @temp_process_step_id = processStep_ID,  @parent_part_number = Part_Number  from product where Product_id = ( select P_Parent_Product_ID from product where Product_Id = @Product_ID )
 				end
 			end
 			
			--select @qty_available = sum(ISNULL(IL_Qty_Balance, Qty_IN)) from inventory_lot, Lot_table where lot_table.lot_id = inventory_lot.lot_id and Lot_current_Status = 'Running' and ( ProcessStep_ID = @temp_process_step_id Or ProcessStep_ID in ( Select Substitute_Product_ProcessStep_Id from Product_Substitutables_Table where Product_ProcessStep_Id = @temp_process_step_id ) ) and location_id = (select Location_id from location where address_id = @vendor_address_Id and L_type = 'Finished Goods')

 			if( @is_sample_sales_order = 0 )
			begin
				select @qty_available = sum(ISNULL(IL_Qty_Balance, Qty_IN)) from inventory_lot, Lot_table where lot_table.lot_id = inventory_lot.lot_id and Lot_current_Status = 'Running' and ( Lot_Category = 'PROD' or Lot_Category = 'ENGG' ) and ( ProcessStep_ID = @temp_process_step_id Or ProcessStep_ID in ( Select Substitute_Product_ProcessStep_Id from Product_Substitutables_Table where Product_ProcessStep_Id = @temp_process_step_id ) ) and location_id = (select Location_id from location where address_id = @vendor_address_Id and L_type = 'Finished Goods')
			end
			else
			begin
				select @qty_available = sum(ISNULL(IL_Qty_Balance, Qty_IN)) from inventory_lot, Lot_table where lot_table.lot_id = inventory_lot.lot_id and Lot_current_Status = 'Running' and ( Lot_Category = 'PROD' ) and ( ProcessStep_ID = @temp_process_step_id Or ProcessStep_ID in ( Select Substitute_Product_ProcessStep_Id from Product_Substitutables_Table where Product_ProcessStep_Id = @temp_process_step_id ) ) and location_id = (select Location_id from location where address_id = @vendor_address_Id and L_type = 'Finished Goods')
			end

 
 			insert into @results( lineItemId, qtyToShip, SalesOrder_ID, COOL_Line_Item_No, Product_ID, COOL_Qty, COOL_Unit_Cost,
 			COOL_Amount, COOL_Requested_Date, COOL_Booking_Date, COOL_Sugg_Schedule_Date_With_Substitutables,
 			COOL_Sugg_Schedule_Date_Without_Substitutables, COOL_Schedule_Date, COOL_Revised_Date, COOL_Is_Cancel,
 			COOL_Status, COOL_Taxable, COOL_Description, COOL_PO_Line_Item_No,Part_Number, Mkt_Part_Number, Customer_Part_Number, Qty_Available, Customer_Label_Number, Parent_Part_Number ) Values
 			( @temp_lineItem, @qty_to_be_shipped, @SalesOrder_ID, @COOL_Line_Item_No, @Product_ID, @COOL_Qty, @COOL_Unit_Cost,
 			@COOL_Amount, @COOL_Requested_Date, @COOL_Booking_Date, @COOL_Sugg_Schedule_Date_With_Substitutables,
 			@COOL_Sugg_Schedule_Date_Without_Substitutables, @COOL_Schedule_Date, @COOL_Revised_Date, @COOL_Is_Cancel,
 			@COOL_Status, @COOL_Taxable, @COOL_Description, @COOL_PO_Line_Item_No, @mfg_part_number, @mkt_part_number, @cutomer_part_number, @qty_available, @cutomer_label_number, @parent_part_number )
 			
 			set @qty_available = 0
 			set @temp_product_type = null 
 			set @cutomer_part_number = '' 
 			set @temp_lineItem = 0
 			set @shipped_qty_sum = 0
 			set @qty_to_be_shipped = 0
 			set @COOL_Line_Item_No = 0
 			set @Product_ID = 0	
 			set @COOL_Qty = 0
 			set @COOL_Unit_Cost = 0.0
 			set @COOL_Amount = 0.0 
 			set @COOL_Requested_Date = ''
 			set @COOL_Booking_Date = ''
 			set @COOL_Sugg_Schedule_Date_With_Substitutables = ''
 			set @COOL_Sugg_Schedule_Date_Without_Substitutables = ''
 			set @COOL_Schedule_Date = ''
 			set @COOL_Revised_Date = ''
 			set @COOL_Is_Cancel = 0
 			set @COOL_Status = ''			
			set @COOL_Taxable = 0
 			set @COOL_Description = ''
 			set @COOL_PO_Line_Item_No = 0
 			set @mfg_part_number = ''
 			set @mkt_part_number = ''
 			set @temp_process_step_id = 0
 			set @parent_product_id = 0
			set @cutomer_label_number = ''
 		end	
  
 		fetch next from lineItemCursor into @temp_lineItem
 
 	end
 	
 	close lineItemCursor
 	deallocate lineItemCursor
 
 	select * from @results
 


